Execute Stored Procedure
AutomatR.OracleDB.Database.ExecuteStoredProcedure
The "Execute Stored Procedure" activity in AutomatR is designed to execute a stored procedure on an Oracle database, providing a flexible way to interact with the database and retrieve results. This activity is particularly useful when you need to call stored procedures for various database operations.
Properties
| Name | Description |
|---|---|
| Input | |
| Connection | Specifies the OracleConnection variable representing the database connection on which the stored procedure will be executed. This variable should be obtained from a previous "Connect" activity or a similar source. |
| Procedure Name | Specifies the name of the stored procedure to be executed. String variables containing the stored procedure name. |
| Parameters | Specifies the input parameters for the stored procedure. You can provide input parameters with their names and values. The direction should be set to "In". |
| Misc | |
| Display Name | Provides a customizable name for the activity displayed in the workflow. The display name enhances clarity and organization within the automation project. String variables containing the desired display name. |
| Optional | |
| Delay | Specifies the amount of time (in seconds) to wait before executing the "Execute Stored Procedure" activity. This can be useful for handling synchronization issues. Integer variables containing the delay duration. Ex.: If the amount of time is 1000 milliseconds or 1 sec, i.e., 1. |
| Command Timeout | Specifies the amount of time (in milliseconds) to wait for the SQL command to run before throwing an error. If not provided, the default timeout is set to 60 seconds. Integer variables containing the command timeout duration. |
| Output | |
| Result | Outputs the result of the stored procedure as a DataSet variable. The DataSet variable can be used in subsequent activities or operations within the workflow. |
| Out Variables | Outputs a Dictionary containing out direction parameters and their values after executing the stored procedure. This is useful when the stored procedure has output parameters. |
How to use:
- Drag and drop the "Execute Stored Procedure" activity onto the workflow.
- Configure the properties by specifying the OracleConnection variable obtained from a previous "Connect" activity, the stored procedure name, input parameters, and other optional parameters.
- Optionally, configure the delay.
- Execute the workflow to execute the stored procedure and retrieve the result as a DataSet and out variables.
Note:
- Ensure that the provided stored procedure name is valid and exists in the Oracle database.
- Input and output parameters are defined using a dictionary where keys are parameter names, and values are Argument objects containing details like direction and datatype.
Example: Consider an example where the "Execute Stored Procedure" activity is used to call a stored procedure named "sp_get_emp_info" that retrieves employee information:
Execute Stored Procedure:
Connection: oracleConnectionVariable
Procedure Name: "sp_get_emp_info"
Parameters:
EmpID: empIdVariable (In)
DeptID: deptIdVariable (In)
Command Timeout: 5000
Result: employeeData
Out Variables: outVariablesDictionary
In this example, the activity is configured to execute the "sp_get_emp_info" stored procedure with input parameters "EmpID" and "DeptID." The result is stored in the DataSet variable "employeeData," and the output parameters are stored in the Dictionary variable "outVariablesDictionary" for further use in the workflow.